'Tac gia: Nguyen Duy Tuan - hrrp://bluesofts.net
'Tel: 0904.210.337 - Email: tuanktcdcn@yahoo.com

Function NhanThuong(DS As Variant)
    Dim ofn As WorksheetFunction
    Dim oVungThuong As Range, oVungDS As Range
    Dim DS1, DS2, ThuongDS, MaxDS, MinDS
    
    Set oVungDS = Application.Range("DOANHSO") 'L vung doanh so, duoc sap xep tang dan
    Set oVungThuong = Application.Range("THUONG") '(gom 2 cot: DOANHSO, THUONG)
    
    Set ofn = Application.WorksheetFunction
        
    MaxDS = ofn.Max(oVungDS)
    If DS > MaxDS Then
        NhanThuong = Int(DS / MaxDS) * ofn.VLookup(MaxDS, oVungThuong, 2, 0)
        DS = DS - Int(DS / MaxDS) * MaxDS
    End If
    'Tim gia tri doanh so thap nhat trong bang doanh so
    'Neu muc DS < muc doanh so thap nhat thi thoat khongtinh nua.
    MinDS = ofn.Min(oVungDS)
    If DS < MinDS Then Exit Function
    
    DS1 = ofn.Lookup(DS, oVungDS) 'Lay ra DS1 gan hoac bang DS (DS1 <= DS)
    ThuongDS = ofn.VLookup(DS1, oVungThuong, 2, 0) ' lay muc thuong ung voi DS1
      
    NhanThuong = NhanThuong + ThuongDS
    
    'Lay phan du (phan con lai cua DS)
    DS2 = DS - DS1
    If DS2 < MinDS Then Exit Function
    If DS2 > 0 Then 'Neu co tiep tuc tinh. Phep De quy
        NhanThuong = NhanThuong + NhanThuong(DS2)
    End If
     
    Set ofn = Nothing
    'Set oThuongDS = Nothing
    Set oVungThuong = Nothing
End Function

Function TimThuong(DOANH_SO As Double, VUNG_DOANHSO, VUNG_THUONG) As Double
    Dim ofn As WorksheetFunction
    Dim DS_DINHMUC As Double, ThuongDS As Double, MaxDS As Double
    
    'VUNG_DOANHSO - L vung doanh so, duoc sap xep tang dan
    'VUNG_THUONG - (gom 2 cot: DOANHSO, THUONG)
    
    Set ofn = Application.WorksheetFunction
        
    MaxDS = ofn.Max(VUNG_DOANHSO)
    If DOANH_SO > MaxDS Then
        TimThuong = Int(DOANH_SO / MaxDS) * ofn.Lookup(MaxDS, VUNG_DOANHSO, VUNG_THUONG)
        'Tinh doanh so con lai de tiep tuc tinh thuong
        DOANH_SO = DOANH_SO - Int(DOANH_SO / MaxDS) * MaxDS
    End If
    'Tim gia tri doanh so thap nhat trong bang doanh so
    'Neu muc DS < muc doanh so thap nhat thi thoat khongtinh nua.
    If DOANH_SO < ofn.Min(VUNG_DOANHSO) Then Exit Function
    
    'Lay ra DS_DINHMUC gan hoac bang DOANH_SO (DS_DINHMUC <= DOANH_SO)
    DS_DINHMUC = ofn.Lookup(DOANH_SO, VUNG_DOANHSO)
    ' lay muc thuong ung voi DS_DINHMUC
    ThuongDS = ofn.Lookup(DS_DINHMUC, VUNG_DOANHSO, VUNG_THUONG)
      
    TimThuong = TimThuong + ThuongDS
    
    'Lay phan du (phan con lai cua DS)
    DS_DINHMUC = DOANH_SO - DS_DINHMUC
    
    If DS_DINHMUC > 0 Then 'Neu co tiep tuc tinh. Phep De quy
        TimThuong = TimThuong + TimThuong(DS_DINHMUC, VUNG_DOANHSO, VUNG_THUONG)
    End If
     
    Set ofn = Nothing
End Function

